package com.wyhello.tube4.dao.impl;

import java.math.BigInteger;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.wyhello.tube4.dao.RealityRecordsDAO;
import com.wyhello.tube4.pojo.Position;
import com.wyhello.tube4.pojo.Reality;

public class RealityRecordsDAOImpl  implements RealityRecordsDAO{
	private SessionFactory sessionFactory;
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	/**
	 * 实际测量信息的录入
	 * @param reality 要录入的信息
	 * @return 返回是否录入成功
	 */
	@Override
	public boolean saveReality(Reality reality) {
		Session session = sessionFactory.getCurrentSession();
		int i = 1;
		try{
			session.save(reality);
		}catch(Exception e){
			i=0;
		}
		if(i==1){
			return true;
		}else{
			return false;
		}
	}
	/**
	 * 对实际测量的信息进行批量删除
	 * @param ids 要删除的id
	 * @return 返回删除的条数
	 */
	@Override
	public int deleteReality(String ids) {
		Session session  =  sessionFactory.getCurrentSession();
		String sql = "delete from reality where id in "+ids;
		int i = session.createSQLQuery(sql).executeUpdate();
		return i;
	}
	/**
	 * 查询所有的实际测量信息
	 * @return 返回所有实际测量的信息
	 */
	@Override
	public List<Reality> queryReality(int offset) {
		Session session  =  sessionFactory.getCurrentSession();
		String hql = "from Reality order by time desc";
		Query query = session.createQuery(hql);
		query.setFirstResult(offset);
		query.setMaxResults(10);
		List<Reality> list = query.list();
		
		return list;
	}
	@Override
	public List<Reality> queryRealTime(int offset,String begin,String end) {
		Session session  =  sessionFactory.getCurrentSession();
		String hql = "from Reality where time between '"+begin+"' AND '"+end+"' order by time desc";
		Query query = session.createQuery(hql);
		query.setFirstResult(offset);
		query.setMaxResults(10);
		List<Reality> list = query.list();
		
		return list;
	}
	/**
	 * 根据id查询实际测量信息
	 * @param id 要查询的id
	 * @return 为ID的实际测量信息
	 */
	@Override
	public Reality getRealityById(int id) {
		Session session  =  sessionFactory.getCurrentSession();
		Reality reality = (Reality) session.get(Reality.class, Integer.valueOf(id));
		return reality;
	}
	/**
	 * 对实际信息进行修改
	 * @param reality 修改后的信息
	 * @return 修改是否成功
	 */
	@Override
	public boolean updateReality(Reality reality) {
		Session session  =  sessionFactory.getCurrentSession();
		try{
			session.update(reality);
		}catch(Exception e){
			return false;
		}
		return true;
	}
	/**
	 * 查询指定位置下实测记录的最大ID值
	 * @param positionId 
	 * @return 
	 */
	@Override
	public int queryRealityMaxId(int positionId) {
		Session session  =  sessionFactory.getCurrentSession();
		String sql = "select max(id) from reality where positionId = " + positionId;
		int id = (Integer) session.createSQLQuery(sql).list().get(0);
		return id;
	}
	/**
	 * 查询实测记录数量
	 * @return
	 */
	public int queryCountReality(){
		Session session  =  sessionFactory.getCurrentSession();
		String sql = "select count(*) from reality";
		BigInteger bi  = (BigInteger) session.createSQLQuery(sql).list().get(0);
		int count = bi.intValue();
		return count;
	}
	public int queryCountReaTime(String begin, String end){
		Session session  =  sessionFactory.getCurrentSession();
		String sql = "select count(*) from reality where time between '"+begin+"' AND '"+end+"'";
		BigInteger bi  = (BigInteger) session.createSQLQuery(sql).list().get(0);
		int count = bi.intValue();
		return count;
	}
	@Override
	public List<Reality> queryRealityTime(int positionId, String begin, 
			String end, String type) {
		Session session  =  sessionFactory.getCurrentSession();
		String sql = "from Reality where time between '"+begin+"' AND '"+end
				+"' AND positionId="+positionId
				+ (type=="wearRate"?"":" AND "+type+" > 0 ") 
				+" order by time desc";
		List<Reality>  realitys = session.createQuery(sql).list();
		return realitys;
	}
	/**
	 * 查询需要报警的记录
	 * @return
	 */
	public List<Reality> queryRealityWarn() {
		Session session  =  sessionFactory.getCurrentSession();
		String hql = "from Reality order by time desc";
		Query query = session.createQuery(hql);
		List<Reality> list = query.list();
		return list;
	}
	
	
	@Override
	public String queryRealityMaxByPosIds(String field, String positionIds,String bagId) {
		Session session  =  sessionFactory.getCurrentSession();
		String sql = "SELECT MAX("+field+") FROM reality WHERE positionId in "+positionIds
				+ (field=="wearRate"?"":" AND "+field+" > 0 ")
				+" AND bagId="+bagId;
		
		List list = session.createSQLQuery(sql).list();
		System.out.println("list:" + list);
		System.out.println("list。get(0):" + list.get(0));
		if (list.get(0) == null){
			return null;
		}	
		String val = list.get(0).toString();
		
		return val;
	}
	@Override
	public String queryRealityMinByPosIds(String field, String positionIds,String bagId) {
		Session session  =  sessionFactory.getCurrentSession();
		String sql = "SELECT MIN("+field+") FROM reality WHERE positionId in "+positionIds
				+ (field=="wearRate"?"":" AND "+field+" > 0 ")
				+" AND bagId="+bagId;
		
		List list = session.createSQLQuery(sql).list();
		if (list.get(0) == null){
			return null;
		}	
		String val = list.get(0).toString();
		return val;
	}
	@Override
	public String queryRealityAvgByPosIds(String field, String positionIds,String bagId) {
		Session session  =  sessionFactory.getCurrentSession();
		String sql = "SELECT AVG("+field+") FROM reality WHERE positionId in "+positionIds
				+ (field=="wearRate"?"":" AND "+field+" > 0 ")
				+" AND bagId="+bagId;
		
		List list = session.createSQLQuery(sql).list();
		if (list.get(0) == null){
			return null;
		}	
		String val = list.get(0).toString();
		return val;
	}
	
	/**
	 * 劣化分析-管排
	 * @return
	 */
	@Override
	public List getAnyliseRow(String field, String begin, String end, int rowId) {
		String sql = "SELECT a.time , " +
				"MAX(a." + field + ") AS 'max', " +
				"MIN(a." + field + ") AS 'min', " +
				"AVG(a." + field + ") AS 'avg'" +
				" FROM reality a,POSITION b,tube c, bag d" +
				" WHERE time between '"+begin+"' AND '"+end+"'" +
				 (field=="wearRate"?"":" AND a."+field+" > 0 ") +
				" AND a.positionId=b.id" +
				" AND b.tubeId = c.id" +
				" AND a.bagId = d.id" + 
				" AND c.resideId = " + rowId + " GROUP BY d.id";
	
		Session session  =  sessionFactory.getCurrentSession();
		
		List list = session.createSQLQuery(sql).list();
		
		return list;
	}
	
	/**
	 * 劣化分析-受热面
	 * @return
	 */
	@Override
	public List getAnyliseSurface(String field, String begin, String end, int surfaceId) {
		String sql = "SELECT a.time , " +
				"MAX(a." + field + ") AS 'max', " +
				"MIN(a." + field + ") AS 'min', " +
				"AVG(a." + field + ") AS 'avg'" +
				" FROM reality a,POSITION b,tube c, bag d, reside e" +
				" WHERE time between '"+begin+"' AND '"+end+"'" +
				 (field=="wearRate"?"":" AND a."+field+" > 0 ")+
				" AND a.positionId=b.id" +
				" AND b.tubeId = c.id" +
				" AND c.resideId = e.id " +
				" AND a.bagId = d.id" + 
				" AND e.parentId = " + surfaceId + " GROUP BY d.id";
	
		//System.out.println(sql);
		Session session  =  sessionFactory.getCurrentSession();
		
		List list = session.createSQLQuery(sql).list();
		
		return list;
	}
	
	/**
	 * 劣化分析-锅炉
	 * @return
	 */
	@Override
	public List getAnyliseBoiler(String field, String begin, String end, int boilerId) {
		String sql = "SELECT a.time , " +
				"MAX(a." + field + ") AS 'max', " +
				"MIN(a." + field + ") AS 'min', " +
				"AVG(a." + field + ") AS 'avg'" +
				" FROM reality a,POSITION b,tube c, bag d, reside e, reside f" +
				" WHERE time between '"+begin+"' AND '"+end+"'" +
				 (field=="wearRate"?"":" AND a."+field+" > 0 ")+
				" AND a." + field + " > 0" +
				" AND a.positionId=b.id" +
				" AND b.tubeId = c.id" +
				" AND c.resideId = e.id " +
				" AND e.parentId = f.id " +
				" AND a.bagId = d.id" + 
				" AND f.parentId = " + boilerId + " GROUP BY d.id";
	
		//System.out.println(sql);
		Session session  =  sessionFactory.getCurrentSession();
		
		List list = session.createSQLQuery(sql).list();
		
		return list;
	}
	
	
	/**
	 * 劣化分析-饼图-管排
	 * @param realField 原始值
	 * @param warnField 预警值
	 * @param rowId 管排id
	 * @return
	 */
	@Override
	public List getRealityPieRow(String realField, String warnField, int rowId){
		
		String operator =  ">";
		if("lifetime".equals(realField)){
			operator = "<";
		}
		
		String sql = "SELECT COUNT(a.id) AS '超标位置数/管排位置总数' FROM reality a, POSITION b,tube c " +
				" WHERE a." + realField + operator + " b." + warnField +
				" AND a.positionId=b.id" +
				" AND b.tubeId=c.id" +
				" AND c.resideId =" + rowId + 
				" UNION ALL" +
				" SELECT COUNT(b.id) FROM POSITION b,tube c" +
				" WHERE b.tubeId=c.id" +
				" AND c.resideId =" + rowId;
		
		//System.out.println(sql);
		Session session  =  sessionFactory.getCurrentSession();
		
		List list = session.createSQLQuery(sql).list();
		
		return list;
		
	}
	
	/**
	 * 劣化分析-饼图-受热面
	 * @param realField 原始值
	 * @param warnField 预警值
	 * @param rowId 受热面id
	 * @return
	 */
	@Override
	public List getRealityPieSurface(String realField, String warnField, int surfaceId){
		
		String operator =  ">";
		if("lifetime".equals(realField)){
			operator = "<";
		}
		
		String sql = "SELECT COUNT(a.id) AS '超标位置数/受热面位置总数' FROM reality a, POSITION b,tube c, reside d " +
				" WHERE a." + realField + operator + " b." + warnField +
				" AND a.positionId=b.id" +
				" AND b.tubeId=c.id" +
				" AND c.resideId =d.id" +
				" AND d.parentId =" + surfaceId + 
				" UNION ALL" +
				" SELECT COUNT(b.id) FROM POSITION b,tube c, reside d " +
				" WHERE b.tubeId=c.id" +
				" AND c.resideId =d.id" + 
				" AND d.parentId =" + surfaceId;
		
		//System.out.println(sql);
		Session session  =  sessionFactory.getCurrentSession();
		
		List list = session.createSQLQuery(sql).list();
		
		return list;
		
	}
	
	
	/**
	 * 劣化分析-饼图-锅炉
	 * @param realField 原始值
	 * @param warnField 预警值
	 * @param rowId 锅炉id
	 * @return
	 */
	@Override
	public List getRealityPieBoiler(String realField, String warnField, int boilerId){
		
		String operator =  ">";
		if("lifetime".equals(realField)){
			operator = "<";
		}
		
		String sql = "SELECT COUNT(a.id) AS '超标位置数/锅炉位置总数' FROM reality a, POSITION b,tube c, reside d, reside e " +
				" WHERE a." + realField + operator + " b." + warnField +
				" AND a.positionId=b.id" +
				" AND b.tubeId=c.id" +
				" AND c.resideId =d.id" +
				" AND d.parentId= e.id" + 
				" AND e.parentId =" + boilerId + 
				" UNION ALL" +
				" SELECT COUNT(b.id) FROM POSITION b,tube c, reside d, reside e " +
				" WHERE b.tubeId=c.id" +
				" AND c.resideId =d.id" + 
				" AND d.parentId= e.id" + 
				" AND e.parentId =" + boilerId;
		
		//System.out.println(sql);
		Session session  =  sessionFactory.getCurrentSession();
		
		List list = session.createSQLQuery(sql).list();
		
		return list;
		
	}
}
